package com.tjtt.tdm.system.dao.impl;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.tjtt.common.utils.StringUtil;
import com.tjtt.tdm.base.HibernateEntityDAO;
import com.tjtt.tdm.base.PageResult;
import com.tjtt.tdm.exception.DaoException;
import com.tjtt.tdm.exception.ServiceException;
import com.tjtt.tdm.system.bean.Organization;
import com.tjtt.tdm.system.dao.OrganizationDao;

public class OrganizationDaoImpl extends HibernateEntityDAO<Organization> implements
		OrganizationDao {
	/**
	 * 查询所有的分类节点
	 */
	public List<Organization> getAllTypes() throws DaoException {
		String hql = "from Organization as entity where entity.delFlag = 1 and entity.orgId  not in (295,296,297,298,315,316,317,335)  order by entity.orgOrder ";
		
		return findByHql(hql);
	}
	
	/**
	 * 查询所有的分类节点
	 */
	public List getAllTypesBFR() throws DaoException {
		return getAllTypesB();
	}

	/**
	 * 查询楼节点
	 */
	public List getAllTypesB() throws DaoException {
		String hql = "select orb.id idd,orb.visualname numbers,orb.parentid  pid,orb.type from SYS_VISUALINFO orb  where orb.type=-1 or orb.type=0 or orb.type=-2";
		
		Query query = this.getSession().createSQLQuery(hql);
		List list = query.list();
		return list;
	}
	/**
	 * 查询层节点
	 */
	public List getAllTypesF() throws DaoException {
		String hql = "select orb.id idd,orb.visualname numbers,orb.parentid  pid,orb.type from SYS_VISUALINFO orb  where orb.type=1";
		
		Query query = this.getSession().createSQLQuery(hql);
		List list = query.list();
		return list;
	}
	/**
	 * 查询房间节点
	 */
	public List getAllTypesR() throws DaoException {
		String hql = "select orb.id idd,orb.visualname numbers,orb.parentid  pid,orb.type from SYS_VISUALINFO orb  where orb.type=2";
		
		Query query = this.getSession().createSQLQuery(hql);
		List list = query.list();
		return list;
	}
	public PageResult findOrganizationByParentId(int orgParentId,
			Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();

		queryCountString.append("select count(*) from SYS_ORGANIZATION where ORG_ID not in("+orgParentId+") and DEL_FLAG=1  start with ORG_ID="+orgParentId+"  connect by prior ORG_ID=ORG_PARENT_ID");
		queryString.append("select t.ORG_ID as id,t.ORG_NO,t.ORG_NAME,t.ORG_DESC from SYS_ORGANIZATION t  where ORG_ID not in("+orgParentId+") and DEL_FLAG=1  start with ORG_ID="+orgParentId+" connect by prior ORG_ID=ORG_PARENT_ID ");
		if (filterMap != null && !filterMap.isEmpty()) {
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append("order by " + sidx + " " + sord + " ");
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	public PageResult queryOrganizationList(Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		
		queryCountString.append("select count(*) from SYS_ORGANIZATION t where 1=1 and t.ORG_PARENT_ID > -1 and t.del_flag=1 ");
		queryString.append("select t.ORG_ID as id,t.ORG_NO,t.ORG_NAME,t.ORG_DESC from SYS_ORGANIZATION t where 1=1 and t.ORG_PARENT_ID > -1 and t.del_flag=1 ");
		if (filterMap != null && !filterMap.isEmpty()) {
			if (null != filterMap.get("orgName")&& !"".equals(filterMap.get("orgName"))) {
				queryString.append("and t.ORG_NAME = '"+ filterMap.get("orgName") + "' ");
				queryCountString.append("and t.ORG_NAME = '"+ filterMap.get("orgName") + "' ");
			}
			if (null != filterMap.get("orgNo")&& !"".equals(filterMap.get("orgNo"))) {
				queryString.append("and t.ORG_NO = '"+ filterMap.get("orgNo") + "' ");
				queryCountString.append("and t.ORG_NO = '"+ filterMap.get("orgNo") + "' ");
			}
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append("order by " + sidx + " " + sord + " ");
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	 
	
	public void delete(Organization organization) {
		super.getHibernateTemplate().delete(organization);
		
	}

	public Organization getOrganizationById(Long id) {
		Organization organization  = (Organization) super.getHibernateTemplate().get(Organization.class ,id);
		return organization;
	}

	/**
	 * 查找用户表里的组织id
	 * @return
	 * @throws ServiceException String
	 * @author 张海娜
	 * @date 2012-10-22 下午03:11:40
	 */
	public PageResult findUserByOrgId(String ids,Map<String, String> filterMap) throws ServiceException {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryString.append(" select u.ORG_ID from SYS_USERS u  left join SYS_ORGANIZATION o   on u.ORG_ID=o.ORG_ID where o.ORG_ID= "+ids+" and u.del_flag=1  ");
		queryCountString.append(" select count(*) from SYS_USERS u  left join SYS_ORGANIZATION o   on u.ORG_ID=o.ORG_ID where o.ORG_ID= "+ids+" and u.del_flag=1  ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	/**
	 * 根据删除标记delflag为1，查询机构名称是否存在
	 * @param orgName
	 * @return PageResult
	 * @author 张海娜
	 * @date 2012-11-13 下午05:33:47
	 */
	public PageResult findOrganizationByDelflag(String orgName,Map<String, String> filterMap,Long orgParentId) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryString.append(" select t.org_id,t.org_name from SYS_organization t where t.org_name = '"+orgName+"' and t.del_flag=1 and t.org_parent_id="+orgParentId+"  ");
		queryCountString.append(" select count(*) from SYS_organization t where t.org_name = '"+orgName+"' and t.del_flag=1 and t.org_parent_id="+orgParentId+"  ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}
	
	
	public PageResult findOrgByDelflag(Long orgId,String orgName,Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryString.append(" select t.org_id,t.org_name from SYS_organization t where t.org_name = '"+orgName+"' and t.del_flag=1 and t.org_parent_id="+orgId+"  ");
		queryCountString.append(" select count(*) from SYS_organization t where t.org_name = '"+orgName+"' and t.del_flag=1 and t.org_parent_id="+orgId+"  ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	@Override
	public PageResult findUserById(int id,Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		queryString.append("select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' and u.org_id = "+id+"");
		StringBuffer countString = new StringBuffer();
		countString.append("select count(*) from (select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' and u.org_id = "+id+") ");
        if (filterMap != null && !filterMap.isEmpty()) {
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append("order by " + sidx + " " + sord + " ");
		}
		return searchBySQL(countString.toString(), queryString.toString(), filterMap);
	}

	@Override
	public PageResult queryUserList(Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		queryString.append("select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1'");
		StringBuffer countString = new StringBuffer();
		countString.append("select count(*) from (select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1')");
        if (filterMap != null && !filterMap.isEmpty()) {
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append("order by " + sidx + " " + sord + " ");
		}
		return searchBySQL(countString.toString(), queryString.toString(), filterMap);
	}

	/**
	 * 查询名称是否存在
	 * @throws Exception void
	 * @author 张海娜
	 * @date 2013-2-21 下午02:07:08
	 */
	public Organization findOrganizationName(String orgName) {
		List orgList = this.getHibernateTemplate().find("from Organization o where o.orgName='" + StringUtil.escapeString(orgName) + "' and o.delFlag=1 ");
		if (orgList != null && orgList.size() > 0) {
			return (Organization) orgList.get(0);
		}
		return null;
	}

	/**
	 * 查询编号是否存在
	 * @throws Exception void
	 * @author 张海娜
	 * @date 2013-2-21 下午02:06:16
	 */
	public Organization findOrganizationByorgNo(String orgNo) {
		List orgList = this.getHibernateTemplate().find("from Organization o where o.orgNo='" + StringUtil.escapeString(orgNo) + "' and o.delFlag=1 ");
		if (orgList != null && orgList.size() > 0) {
			return (Organization) orgList.get(0);
		}
		return null;
	}

	@Override
	public PageResult findOrganizationorgNoByDelflag(String orgNo, Map filterMap,Long pId) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryString.append(" select t.org_id,t.org_no from SYS_organization t where t.org_no = '"+orgNo+"' and t.del_flag=1 and t.org_parent_id="+pId+" ");
		queryCountString.append(" select count(*) from SYS_organization t where t.org_no = '"+orgNo+"' and t.del_flag=1 and t.org_parent_id="+pId+" ");
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	@Override
	public PageResult queryuserByOrganationid(String userByOrganationid,
			Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();

		if("wcsq".equals(userByOrganationid)){//外出授权查询用户
			queryCountString.append("select count(*) from (select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' )");
			queryString.append("select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' ");
		}else{
			queryCountString.append("select count(*) from (select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' and o.ORG_PARENT_ID ="+userByOrganationid+" )");
			queryString.append("select distinct u.userid, u.loginname,u.username,u.sex,u.job_name, o.org_name,u.active,u.lock_flag from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' and o.ORG_PARENT_ID ="+userByOrganationid+" ");
		}
		if (filterMap != null && !filterMap.isEmpty()) {
			
			String sidx = (String) filterMap.get("sidx");
			String sord = (String) filterMap.get("sord");
			queryString.append("order by " + sidx + " " + sord + " ");
		}
		return searchBySQL(queryCountString.toString(), queryString.toString(), filterMap);
	}

	@Override
	public boolean isUsingItem(String ids, String[] tables, String[] fieldNames) {
		List list = null;
		for(int i=0; i<tables.length; i++){
			String sqlStr = "select * from " + tables[i] + " where " + fieldNames[i] + " = '"+ids+"'";
			Query query = getSession().createSQLQuery(sqlStr);
			list = query.list();
			if(list.size()>0){
				return true;
			}
		}
		return false;
	}

	@Override
	public List<Organization> getAllTypes4() throws DaoException {
		String hql = "from Organization as entity where entity.delFlag = 1 and entity.orgId in (1,295,296,297,298) order by entity.orgId ";
		
		return findByHql(hql);
	}

	@Override
	public List<Organization> getAllTypesBuFen() throws DaoException {
		String hql = "from Organization as entity where entity.delFlag = 1 and entity.orgId in (295,296,297,335,298,315,316,317,1) order by entity.orgId ";
		
		return findByHql(hql);
	}
}
